#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
获取 Gitee 组织所有仓库及其贡献者信息，并存入 SQLite 数据库
"""

import time
import requests
from bs4 import BeautifulSoup
from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.chrome.service import Service
from selenium.webdriver.chrome.options import Options
import os
from dotenv import load_dotenv
import re
import sqlite3

# ==================== 配置区 ====================
GITEE_ORG_NAME = "spark-store-project"
driver_path = None
DB_NAME = "gitee_data.db"

# ==================== 数据库操作 ====================

def init_db():
    """
    初始化数据库和表结构。
    """
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()

    # 创建仓库表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS repositories (
            repo_id INTEGER PRIMARY KEY,
            repo_name TEXT NOT NULL UNIQUE,
            repo_url TEXT NOT NULL
        )
    ''')

    # 创建贡献者表，包含外键关联
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS contributors (
            contributor_id INTEGER PRIMARY KEY,
            repo_id INTEGER NOT NULL,
            username TEXT NOT NULL,
            user_profile_url TEXT,
            avatar_url TEXT,
            commits INTEGER,
            FOREIGN KEY (repo_id) REFERENCES repositories(repo_id)
        )
    ''')
    conn.commit()
    conn.close()
    print("数据库表已初始化。")

def clear_db_data():
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    cursor.execute("DELETE FROM contributors")
    cursor.execute("DELETE FROM repositories")
    conn.commit()
    conn.close()
    print("数据库数据已清空。")
def insert_repo(repo_name, repo_url):
    """
    将仓库信息插入到数据库中，如果已存在则忽略。
    返回新插入的仓库ID。
    """
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    repo_id = None
    try:
        cursor.execute("INSERT INTO repositories (repo_name, repo_url) VALUES (?, ?)", (repo_name, repo_url))
        repo_id = cursor.lastrowid
        conn.commit()
        print(f"新仓库 '{repo_name}' 已添加，ID: {repo_id}")
    except sqlite3.IntegrityError:
        print(f"仓库 '{repo_name}' 已存在。")
        cursor.execute("SELECT repo_id FROM repositories WHERE repo_name = ?", (repo_name,))
        repo_id = cursor.fetchone()[0]
    finally:
        conn.close()
    return repo_id


def insert_contributors(repo_id, contributors_list):
    """
    将贡献者信息批量插入到数据库中。
    """
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    
    data = [(
        repo_id,
        c['username'],
        c['user_profile_url'],
        c['avatar_url'],
        c['commits']
    ) for c in contributors_list]
    
    try:
        cursor.executemany("INSERT INTO contributors (repo_id, username, user_profile_url, avatar_url, commits) VALUES (?, ?, ?, ?, ?)", data)
        conn.commit()
        print(f"已为仓库 ID {repo_id} 批量插入 {len(contributors_list)} 位贡献者。")
    except sqlite3.Error as e:
        print(f"批量插入贡献者失败: {e}")
    finally:
        conn.close()

# ==================== 自动化登录并获取 Cookie ====================

def get_gitee_cookies(username, password):
    """
    使用 Selenium 登录 Gitee 并获取会话 Cookie。
    """
    print("正在启动浏览器并登录 Gitee...")
    chrome_options = Options()
    chrome_options.add_argument("--headless")
    chrome_options.add_argument("--disable-gpu")
    chrome_options.add_argument("--window-size=1920,1080")
    
    if driver_path:
        driver = webdriver.Chrome(service=driver_path, options=chrome_options)
    else:
        driver = webdriver.Chrome(options=chrome_options)

    driver.get("https://gitee.com/login")
    time.sleep(2)

    try:
        driver.find_element(By.ID, "user_login").send_keys(username)
        driver.find_element(By.ID, "user_password").send_keys(password)
        driver.find_element(By.NAME, "commit").click()
        
        time.sleep(5)

        if "login" in driver.current_url:
            print("登录失败，请检查用户名和密码。")
            return None
            
        print("登录成功，正在获取会话 Cookie...")
        cookies = {cookie['name']: cookie['value'] for cookie in driver.get_cookies()}
        return cookies

    except Exception as e:
        print(f"登录过程中发生错误: {e}")
        return None
        
    finally:
        driver.quit()

# ==================== Cookie 存储与加载 ====================

def save_cookies(cookies):
    """
    将 requests 格式的 cookies 保存到 .env 文件中。
    """
    try:
        load_dotenv()
        with open('.env', 'r') as f:
            lines = f.readlines()
        
        with open('.env', 'w') as f:
            for line in lines:
                if not line.strip().startswith('GITEE_COOKIE_'):
                    f.write(line)
            
            f.write('\n# Gitee Cookies (generated by script)\n')
            for name, value in cookies.items():
                f.write(f'GITEE_COOKIE_{name.upper()}="{value}"\n')
        print("新的 Cookie 已成功保存到 .env 文件。")
    except Exception as e:
        print(f"保存 Cookie 到 .env 失败: {e}")


def load_cookies():
    """
    从 .env 文件中加载 cookies 并转换为 requests 格式。
    """
    cookies = {}
    load_dotenv()
    
    for key, value in os.environ.items():
        if key.startswith('GITEE_COOKIE_'):
            cookie_name = key.replace('GITEE_COOKIE_', '').lower()
            cookies[cookie_name] = value
            
    return cookies


def is_cookie_valid(cookies):
    """
    检查 cookie 是否仍然有效。
    """
    test_url = "https://gitee.com/dashboard"
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
    }
    
    if not cookies:
        return False
        
    try:
        print("\n正在验证现有 Cookie 的有效性...")
        response = requests.get(test_url, headers=headers, cookies=cookies, allow_redirects=False)
        if response.status_code == 200:
            print("Cookie 仍然有效。")
            return True
        else:
            print(f"Cookie 已失效，状态码: {response.status_code}")
            return False
    except requests.exceptions.RequestException as e:
        print(f"Cookie 验证请求失败: {e}")
        return False

# ==================== 使用 Gitee API 获取仓库列表 ====================

def get_org_repos(org_name):
    """
    使用 Gitee API 获取组织下的所有仓库。
    """
    print(f"\n正在使用 API 获取组织 '{org_name}' 的所有仓库...")
    repos_url = f"https://gitee.com/api/v5/orgs/{org_name}/repos"
    
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
    }
    
    all_repos = []
    page = 1
    per_page = 100
    
    while True:
        params = {
            'page': page,
            'per_page': per_page,
        }
        try:
            response = requests.get(repos_url, headers=headers, params=params)
            response.raise_for_status()
            
            repos = response.json()
            if not repos:
                break
            
            all_repos.extend(repos)
            print(f"已获取第 {page} 页仓库，共 {len(all_repos)} 个。")
            page += 1
            
        except requests.exceptions.RequestException as e:
            print(f"获取组织仓库列表失败: {e}")
            return None
        except Exception as e:
            print(f"解析 API 响应失败: {e}")
            return None
    
    return all_repos

# ==================== 使用 Cookie 爬取贡献者页面并解析 ====================

def get_contributors_with_cookies(org_name, repo_name, cookies):
    """
    使用获取到的 Cookie 爬取贡献者页面并解析头像、用户名和贡献数量。
    """
    contributors_url = f"https://gitee.com/{org_name}/{repo_name}/contributors"
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
    }
    
    print(f"\n-> 正在请求仓库 '{repo_name}' 的贡献者页面...")
    
    try:
        response = requests.get(contributors_url, headers=headers, cookies=cookies)
        response.raise_for_status()

        soup = BeautifulSoup(response.text, 'html.parser')
        user_items = soup.find_all('div', class_='item user-list-item')

        if not user_items:
            print(f"未找到仓库 '{repo_name}' 的贡献者列表。")
            return []

        contributors_data = []

        for item in user_items:
            try:
                avatar_tag = item.find('img', class_=lambda x: x and 'ui avatar' in x)
                avatar_url = avatar_tag['src'] if avatar_tag and avatar_tag.has_attr('src') else None
                
                username_tag = item.find('a', class_='username')
                username = username_tag.text.strip() if username_tag else None
                
                user_profile_path = username_tag['href'] if username_tag and username_tag.has_attr('href') else None
                if user_profile_path and not user_profile_path.startswith('mailto:'):
                    user_profile_url = f"https://gitee.com{user_profile_path}"
                else:
                    user_profile_url = user_profile_path
                
                commits_tag = item.find('a', class_='sub-info')
                commits_text = commits_tag.text.strip() if commits_tag else 'Commits: 0'
                commits_count = int(re.search(r'\d+', commits_text).group()) if re.search(r'\d+', commits_text) else 0
                
                if username: # 只存储有用户名的贡献者
                    contributors_data.append({
                        'username': username,
                        'user_profile_url': user_profile_url,
                        'avatar_url': avatar_url,
                        'commits': commits_count
                    })
            except (TypeError, AttributeError, ValueError) as e:
                print(f"解析贡献者信息出错: {e}. 跳过此条目.")
                continue
                
        return contributors_data

    except requests.exceptions.RequestException as e:
        print(f"请求页面出错: {e}")
        return []
    except Exception as e:
        print(f"解析页面出错: {e}")
        return []

# ==================== 主程序入口 ====================

def main():
    # 1. 初始化数据库
    init_db()
    
    # 2.有数据则清空
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) FROM repositories")
    repo_count = cursor.fetchone()[0]
    conn.close()
    if repo_count > 0:
        clear_db_data()


    # 3. 尝试加载或获取 Cookie
    cookies = load_cookies()
    if not cookies or not is_cookie_valid(cookies):
        gitee_username = os.getenv("GITEE_USERNAME")
        gitee_password = os.getenv("GITEE_PASSWORD")
        if not gitee_username or not gitee_password:
            print("错误：未找到 GITEE_USERNAME 或 GITEE_PASSWORD。")
            return
            
        print("Cookie 无效或不存在，正在重新登录...")
        new_cookies = get_gitee_cookies(gitee_username, gitee_password)
        if new_cookies:
            cookies = new_cookies
            save_cookies(cookies)
        else:
            print("无法获取 Gitee 会话，脚本终止。")
            return

    # 4. 使用 API 获取组织所有仓库
    repos = get_org_repos(GITEE_ORG_NAME)
    
    if not repos:
        print("无法获取组织仓库列表，脚本终止。")
        return
        
    print(f"\n成功获取 {len(repos)} 个仓库。开始逐一获取贡献者信息并存入数据库。")
    
    # 5. 遍历每个仓库并处理
    for repo in repos:
        repo_name_display = repo['name']
        repo_path = repo['path']
        repo_url = repo['html_url']

        print("\n" + "=" * 50)
        print(f"开始处理仓库: {repo_name_display}")
        
        # 将仓库信息存入数据库，并获取其ID
        repo_id = insert_repo(repo_name_display, repo_url)
        
        # 爬取贡献者信息
        contributors_data = get_contributors_with_cookies(GITEE_ORG_NAME, repo_path, cookies)
        
        if contributors_data:
            # 将贡献者信息存入数据库
            insert_contributors(repo_id, contributors_data)
        else:
            print("该仓库没有贡献者数据。")
            
        time.sleep(1) # 增加延迟，避免请求过于频繁

if __name__ == "__main__":
    main()